package com.kaikeba.dao.impl;

import com.kaikeba.bean.UserExpress;
import com.kaikeba.dao.BaseUserExpressDao;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserExpressMysql implements BaseUserExpressDao {
    //用于查询总的用户，与当日新增用户数量
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) data_size,COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) data_day FROM USER_EXPRESS";
    //用于查询全部用户的所有信息
    public static final String SQL_FIND_ALL = "SELECT * FROM USER_EXPRESS";
    //用于分页查询用户的所有信息
    public static final String SQL_FIND_BY_LIMIT = "SELECT * FROM USER_EXPRESS LIMIT ?,?";
    //通过用户手机号查询用户所有信息
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM USER_EXPRESS WHERE USERPHONE=?";
    //用户注册
    public static final String SQL_INSERT = "INSERT INTO USER_EXPRESS(USERNAME,USERPHONE,ID_CARD,PASSWORD,CREATETIME) VALUES(?,?,?,?,NOW())";
    //用户信息修改
    public static final String SQL_UPDATE = "UPDATE USER_EXPRESS SET USERNAME=?,USERPHONE=?,ID_CARD=?,PASSWORD=? WHERE ID=?";
    //通过用户id删除用户所有信息
    public static final String SQL_DELETE = "DELETE FROM USER_EXPRESS WHERE ID=?";
    //通过用户身份证号查询用户信息
    public static final String SQL_FIND_BY_ID_CARD = "SELECT * FROM USER_EXPRESS WHERE ID_CARD=?";
    /**
     * 用于查询总人数 + 日注册量
     *
     * @return 返回一个集合{size:总数,day:日注册量}
     */
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        //连接数据库
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            // 执行
            resultSet = state.executeQuery();
            if(resultSet.next()){
                int data_size = resultSet.getInt("data_size");
                int data_day = resultSet.getInt("data_day");
                data.put("data_size",data_size);
                data.put("data_day",data_day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放资源
            DruidUtil.close(conn,state,resultSet);
        }
        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit      是否分页标记，true表示分页，false表示查询所有快递
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<UserExpress> findAll(boolean limit, int offset, int pageNumber) {
        List<UserExpress> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            if (limit) {
                //要分页
                state = conn.prepareStatement(SQL_FIND_BY_LIMIT);
                //填充参数
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            }else{
                state = conn.prepareStatement(SQL_FIND_ALL);
            }
            //执行
            resultSet = state.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userPhone = resultSet.getString("userPhone");
                String password = resultSet.getString("password");
                String id_card = resultSet.getString("id_card");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                UserExpress e = new UserExpress(id,username,userPhone,id_card,password,createtime,logintime);
                data.add(e);
                }
        } catch(SQLException throwables){
            throwables.printStackTrace();
        }finally{
            DruidUtil.close(conn, state, resultSet);
        }
        return data;
    }

    /**
     * 通过用户电话的号码查询用户所有信息
     *"SELECT * FROM USER_EXPRESS WHERE USERPHONE=?"
     * @param userPhone 用户电话号码
     * @return 返回用户信息
     */
    @Override
    public UserExpress findByPhone(String userPhone) {
        // 获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //填充参数
            state.setString(1,userPhone);
            // 执行语句
            resultSet = state.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userphone = resultSet.getString("userphone");
                String id_card = resultSet.getString("ID_card");
                String password = resultSet.getString("password");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                UserExpress e = new UserExpress(id,username,userphone,id_card,password,createtime,logintime);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 通过用户身份证号码查询用户所有信息
     *
     * @param idCard 用户身份证号码
     * @return 返回用户信息
     */
    @Override
    public UserExpress findByIdCard(String idCard) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_ID_CARD);
            //填充参数
            state.setString(1,idCard);
            //执行
            resultSet = state.executeQuery();
            if(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String userphone = resultSet.getString("userphone");
                String id_card = resultSet.getString("ID_card");
                String password = resultSet.getString("password");
                Timestamp createtime = resultSet.getTimestamp("createtime");
                Timestamp logintime = resultSet.getTimestamp("logintime");
                UserExpress e = new UserExpress(id,username,userphone,id_card,password,createtime,logintime);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,resultSet);
        }
        return null;
    }

    /**
     * 注册用户
     *INSERT INTO USER_EXPRESS(USERNAME,USERPHONE,ID_CARD,PASSWORD,CREATETIME) VALUES(?,?,?,?,NOW())
     * @param e 用户信息
     * @return true表示注册成功 false表示注册失败
     */
    @Override
    public boolean insert(UserExpress e) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        // 预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_INSERT);
            // 填充参数
            state.setString(1,e.getUsername());
            state.setString(2,e.getUserPhone());
            state.setString(3,e.getIDCard());
            state.setString(4,e.getPassword());
            //执行
            int i = state.executeUpdate();
            if(i > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 通过用户id修改用户信息
     *UPDATE USER_EXPRESS SET USERNAME=?,USERPHONE=?,ID_CARD=?,PASSWORD=?,CREATETIME=NOW() WHERE ID=?
     * @param id             用户id
     * @param newUserExpress 用户新的信息
     * @return true 修改成功  false 修改失败
     */
    @Override
    public boolean update(int id, UserExpress newUserExpress) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            //填充参数
            state.setString(1,newUserExpress.getUsername());
            state.setString(2,newUserExpress.getUserPhone());
            state.setString(3,newUserExpress.getIDCard());
            state.setString(4,newUserExpress.getPassword());
            state.setInt(5,id);
            //执行
            int i = state.executeUpdate();
            if(i > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据用户id删除快递
     *
     * @param id 用户id
     * @return true表示删除成功 false 表示删除失败
     */
    @Override
    public boolean delete(int id) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            //填充参数
            state.setInt(1,id);
            //执行语句
            int i = state.executeUpdate();
            if(i > 0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
